package myPetStore.persistence.impl;

import myPetStore.domain.Order;
import myPetStore.persistence.DBUtil;
import myPetStore.persistence.OrderDao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {
    private static final String GET_LAST_ONE="SELECT * FROM ORDERS";
    private static final String GET_ORDER_BY_USERNAME =
            "SELECT\n" +
                    "      BILLADDR1 AS billAddress1,\n" +
                    "      BILLADDR2 AS billAddress2,\n" +
                    "      BILLCITY,\n" +
                    "      BILLCOUNTRY,\n" +
                    "      BILLSTATE,\n" +
                    "      BILLTOFIRSTNAME,\n" +
                    "      BILLTOLASTNAME,\n" +
                    "      BILLZIP,\n" +
                    "      SHIPADDR1 AS shipAddress1,\n" +
                    "      SHIPADDR2 AS shipAddress2,\n" +
                    "      SHIPCITY,\n" +
                    "      SHIPCOUNTRY,\n" +
                    "      SHIPSTATE,\n" +
                    "      SHIPTOFIRSTNAME,\n" +
                    "      SHIPTOLASTNAME,\n" +
                    "      SHIPZIP,\n" +
                    "      CARDTYPE,\n" +
                    "      COURIER,\n" +
                    "      CREDITCARD,\n" +
                    "      EXPRDATE AS expiryDate,\n" +
                    "      LOCALE,\n" +
                    "      ORDERDATE,\n" +
                    "      ORDERS.ORDERID,\n" +
                    "      TOTALPRICE,\n" +
                    "      USERID AS usernam\n" +
                    "    FROM ORDERS\n" +
                    "    WHERE ORDERS.USERID = ?\n";
    private static final String GET_ORDER=
            "select\n" +
                    "      BILLADDR1 AS billAddress1,\n" +
                    "      BILLADDR2 AS billAddress2,\n" +
                    "      BILLCITY,\n" +
                    "      BILLCOUNTRY,\n" +
                    "      BILLSTATE,\n" +
                    "      BILLTOFIRSTNAME,\n" +
                    "      BILLTOLASTNAME,\n" +
                    "      BILLZIP,\n" +
                    "      SHIPADDR1 AS shipAddress1,\n" +
                    "      SHIPADDR2 AS shipAddress2,\n" +
                    "      SHIPCITY,\n" +
                    "      SHIPCOUNTRY,\n" +
                    "      SHIPSTATE,\n" +
                    "      SHIPTOFIRSTNAME,\n" +
                    "      SHIPTOLASTNAME,\n" +
                    "      SHIPZIP,\n" +
                    "      CARDTYPE,\n" +
                    "      COURIER,\n" +
                    "      CREDITCARD,\n" +
                    "      EXPRDATE AS expiryDate,\n" +
                    "      LOCALE,\n" +
                    "      ORDERDATE,\n" +
                    "      ORDERS.ORDERID,\n" +
                    "      TOTALPRICE,\n" +
                    "      USERID AS username,\n" +
                    "      STATUS\n" +
                    "    FROM ORDERS, ORDERSTATUS\n" +
                    "    WHERE ORDERS.ORDERID =?\n" +
                    "      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID";
    private static final String INSERT_ORDER=
            "INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE,\n" +
                    "      SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY,\n" +
                    "      COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME,\n" +
                    "      CREDITCARD, EXPRDATE, CARDTYPE, LOCALE)\n" +
                    "    VALUES(?, ?, ?, ?, ?,?, ?,\n" +
                    "      ?, ?, ?,?, ?,?,?,\n" +
                    "      ?, ?, ?,?,?, ?, ?,\n" +
                    "      ?, ?, ?, ?)";
    private static final String INSERT_ORDER_STATUS=
            "INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)\n" +
                    "    VALUES (?, ?," +
                    " ?, ?)";


    @Override
    public int getLastNum() {
        int num=0;
        try{
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_LAST_ONE);
            ResultSet resultSet=preparedStatement.executeQuery();
            while(resultSet.next()){
                num=resultSet.getInt("orderid")+1;
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return num;
    }

    @Override
    public List<Order> getOrdersByUsername(String var1) {
        List<Order> orderList=new ArrayList<>();
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement statement=connection.prepareStatement(GET_ORDER_BY_USERNAME);
            statement.setString(1,var1);
            ResultSet resultSet=statement.executeQuery();
            while (resultSet.next()){
                Order order=new Order();
                order.setBillAddress1(resultSet.getString(1));
                order.setBillAddress2(resultSet.getString(2));
                order.setBillCity(resultSet.getString(3));
                order.setBillCountry(resultSet.getString(4));
                order.setBillState(resultSet.getString(5));
                order.setBillToFirstName(resultSet.getString(6));
                order.setBillToLastName(resultSet.getString(7));
                order.setBillZip(resultSet.getString(8));
                order.setShipAddress1(resultSet.getString(9));
                order.setShipAddress2(resultSet.getString(10));
                order.setShipCity(resultSet.getString(11));
                order.setShipCountry(resultSet.getString(12));
                order.setShipState(resultSet.getString(13));
                order.setShipToFirstName(resultSet.getString(14));
                order.setShipToLastName(resultSet.getString(15));
                order.setShipZip(resultSet.getString(16));
                order.setCardType(resultSet.getString(17));
                order.setCourier(resultSet.getString(18));
                order.setCreditCard(resultSet.getString(19));
                order.setExpiryDate(resultSet.getString(20));
                order.setLocale(resultSet.getString(21));
                order.setOrderDate(resultSet.getDate(22));
                order.setOrderId(resultSet.getInt(23));
                order.setTotalPrice(resultSet.getBigDecimal(24));
                order.setUsername(resultSet.getString(25));
                order.setStatus("P");
                orderList.add(order);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception ex){
            ex.printStackTrace();
        }
        return orderList;
    }

    @Override
    public Order getOrder(int var1) {
        Order order=null;
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement statement=connection.prepareStatement(GET_ORDER);
            statement.setInt(1,var1);
            ResultSet resultSet=statement.executeQuery();
            if (resultSet.next()){
                order=new Order();
                order.setBillAddress1(resultSet.getString(1));
                order.setBillAddress2(resultSet.getString(2));
                order.setBillCity(resultSet.getString(3));
                order.setBillCountry(resultSet.getString(4));
                order.setBillState(resultSet.getString(5));
                order.setBillToFirstName(resultSet.getString(6));
                order.setBillToLastName(resultSet.getString(7));
                order.setBillZip(resultSet.getString(8));
                order.setShipAddress1(resultSet.getString(9));
                order.setShipAddress2(resultSet.getString(10));
                order.setShipCity(resultSet.getString(11));
                order.setShipCountry(resultSet.getString(12));
                order.setShipState(resultSet.getString(13));
                order.setShipToFirstName(resultSet.getString(14));
                order.setShipToLastName(resultSet.getString(15));
                order.setShipZip(resultSet.getString(16));
                order.setCardType(resultSet.getString(17));
                order.setCourier(resultSet.getString(18));
                order.setCreditCard(resultSet.getString(19));
                order.setExpiryDate(resultSet.getString(20));
                order.setLocale(resultSet.getString(21));
                order.setOrderDate(resultSet.getDate(22));
                order.setOrderId(resultSet.getInt(23));
                order.setTotalPrice(resultSet.getBigDecimal(24));
                order.setUsername(resultSet.getString(25));
                order.setStatus(resultSet.getString(26));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception ex){
            ex.printStackTrace();
        }
        return order;
    }

    @Override
    public void insertOrder(Order var1) {
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement statement=connection.prepareStatement(INSERT_ORDER);
            statement.setInt(1,var1.getOrderId());
            statement.setString(2, var1.getUsername());
            java.sql.Timestamp date= new java.sql.Timestamp(var1.getOrderDate().getTime());
            statement.setTimestamp(3,date);
            statement.setString(4,var1.getShipAddress1());
            statement.setString(5,var1.getShipAddress2());
            statement.setString(6,var1.getShipCity());
            statement.setString(7,var1.getShipState());
            statement.setString(8,var1.getShipZip());
            statement.setString(9,var1.getShipCountry());
            statement.setString(10,var1.getBillAddress1());
            statement.setString(11,var1.getBillAddress2());
            statement.setString(12,var1.getBillCity());
            statement.setString(13,var1.getBillState());
            statement.setString(14,var1.getBillZip());
            statement.setString(15,var1.getBillCountry());
            statement.setString(16,var1.getCourier());
            statement.setBigDecimal(17,var1.getTotalPrice());
            statement.setString(18,var1.getBillToFirstName());
            statement.setString(19,var1.getBillToLastName());
            statement.setString(20,var1.getShipToFirstName());
            statement.setString(21,var1.getShipToLastName());
            statement.setString(22,var1.getCreditCard());
            statement.setString(23,var1.getExpiryDate());
            statement.setString(24,var1.getCardType());
            statement.setString(25,var1.getLocale());
            statement.executeUpdate();
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception ex){
            ex.printStackTrace();
        }

    }

    @Override
    public void insertOrderStatus(Order var1) {
        try {
            Connection connection= DBUtil.getConnection();
            for (int i = 0; i < var1.getLineItems().size(); i++) {
                PreparedStatement statement=connection.prepareStatement(INSERT_ORDER_STATUS);
                statement.setInt(1,var1.getOrderId());
//            statement.setArray(2, var1.getLineItems());
                statement.setInt(2,var1.getLineItems().get(i).getLineNumber());
                statement.setDate(3, (Date) var1.getOrderDate());
                statement.setString(4,var1.getStatus());
                statement.executeUpdate();
                DBUtil.closeStatement(statement);
            }
            DBUtil.closeConnection(connection);
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }
}
